﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
    public class AcademyDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoAcademy model)
        {
            model.ACA_ID = Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Academy] ");
            sql.Append("([ACA_ID],[ACA_NAME],[ACA_CODE],[ACA_SHORT],[ACA_ENNAME],[ACA_IsTeach],[ACA_REMARK],[ACA_LOCAL],[ACA_IsShow]) ");
            sql.Append("VALUES ");
            sql.Append("(@ACA_ID,@ACA_NAME,@ACA_CODE,@ACA_SHORT,@ACA_ENNAME,@ACA_IsTeach,@ACA_REMARK,@ACA_LOCAL,@ACA_IsShow)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoAcademy model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Academy] SET ");
            sql.Append("ACA_NAME=@ACA_NAME,ACA_CODE=@ACA_CODE,ACA_SHORT=@ACA_SHORT,ACA_ENNAME=@ACA_ENNAME,ACA_IsTeach=@ACA_IsTeach,ACA_REMARK=@ACA_REMARK,ACA_LOCAL=@ACA_LOCAL,ACA_IsShow=@ACA_IsShow");
            sql.Append(" WHERE ");
            sql.Append("ACA_ID=@ACA_ID");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string aCA_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Academy] SET ");
            sql.Append("ACA_IsShow='false'");
            sql.Append(" WHERE ");
            sql.Append("ACA_ID=@ACA_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@ACA_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = aCA_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }
        /// <summary>
        /// 通过校区id来删除学院
        /// </summary>
        /// <param name="localId"></param>
        /// <returns>返回受影响的行数</returns>
        public int DeleteAcademyByLocalId(string localId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Academy] SET ");
            sql.Append(" ACA_IsShow='false'");
            sql.Append(" WHERE ");
            sql.Append("ACA_LOCAL=@ACA_LOCAL");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@ACA_LOCAL", SqlDbType.NVarChar, 50);
            parameter[0].Value = localId;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }
        /// <summary>
        /// 获取一条记录 取得某个学院的信息
        /// </summary>
        /// <returns></returns>
        public BoAcademy GetModel(string aCA_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT ACA_ID,ACA_NAME,ACA_IsShow,ACA_CODE,ACA_SHORT,ACA_ENNAME,ACA_IsTeach,ACA_REMARK,ACA_LOCAL,LOC_NAME ");
            sql.Append("FROM Academy AS A LEFT JOIN Local AS L ON A.ACA_LOCAL=L.LOC_ID ");
            sql.Append(" WHERE ");
            sql.Append("ACA_ID=@ACA_ID AND ACA_IsShow='true'");
            SqlParameter[] pr = new SqlParameter[]
            {
                new SqlParameter("@ACA_ID",SqlDbType.NVarChar, 50)
            };
            pr[0].Value = aCA_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), pr);
            IList<BoAcademy> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }
        //取得所有学院的信息 不包括假删除的
        public List<BoAcademy> GetAcademies()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT ACA_ID,ACA_NAME,ACA_IsShow,ACA_CODE,ACA_SHORT,ACA_ENNAME,ACA_IsTeach,ACA_REMARK,ACA_LOCAL,LOC_NAME ");
            sql.Append("FROM Academy AS A LEFT JOIN Local AS L ON A.ACA_LOCAL=L.LOC_ID ");
            sql.Append(" WHERE ");
            sql.Append("ACA_IsShow='true'");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            List<BoAcademy> list = GetList(table);
            return list != null && list.Count > 0 ? list : null;
        }
        /// <summary>
        /// 得到分页的数据列表
        /// </summary>
        /// <param name="orderCon">排序条件</param>
        /// <param name="sqlCon">查询条件</param>
        /// <param name="pagesize">每页显示大小</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="records">返回查询到的记录</param>
        /// <returns>返回对象列表</returns>
        public IList<BoAcademy> getPageCompetitions(string sqlCon, string orderCon, int pagesize, int pageIndex, out int records)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ACA_ID,ACA_NAME,ACA_IsShow,ACA_CODE,ACA_SHORT,ACA_ENNAME,ACA_IsTeach,ACA_REMARK,ACA_LOCAL,LOC_NAME ");
            sql.Append("FROM Academy AS A LEFT JOIN Local AS L ON A.ACA_LOCAL=L.LOC_ID ");
            sql.Append(" WHERE ");
            sql.Append("ACA_IsShow='true'");
            if (!string.IsNullOrEmpty(sqlCon))
            {
                sql.Append("and ");
                sql.Append(sqlCon);
            }
            DataTable table = dbhelper.ExecuteSp_Page(sql.ToString(), orderCon, pagesize, pageIndex, out records);
            IList<BoAcademy> list = GetList(table);
            return list;
        }
        //取得某校区的学院
        public IList<BoAcademy> GetAcademyByLocal(string localId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT ACA_ID,ACA_NAME,ACA_IsShow,ACA_CODE,ACA_SHORT,ACA_ENNAME,ACA_IsTeach,ACA_REMARK,ACA_LOCAL,LOC_NAME ");
            sql.Append("FROM Academy AS A LEFT JOIN Local AS L ON A.ACA_LOCAL=L.LOC_ID ");
            sql.Append(" WHERE ");
            sql.Append("ACA_LOCAL=@ACA_LOCAL AND ACA_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@ACA_LOCAL", SqlDbType.NVarChar, 50);
            parameter[0].Value = localId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            List<BoAcademy> list = GetList(table);
            return list;
        }

        /// <summary>
        /// 通过学院的名字来获取学院对象
        /// </summary>
        /// <param name="AcademyName">学院的名字</param>
        /// <returns>返回学院的对象</returns>
        public List<BoAcademy> getAcademyByName(string AcademyName)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ACA_ID,ACA_NAME,ACA_IsShow,ACA_CODE,ACA_SHORT,ACA_ENNAME,ACA_IsTeach,ACA_REMARK,ACA_LOCAL,LOC_NAME ");
            sql.Append("FROM Academy AS A LEFT JOIN Local AS L ON A.ACA_LOCAL=L.LOC_ID ");
            sql.Append(" WHERE ");
            sql.Append(" ACA_IsShow='true' and ACA_NAME=@ACA_NAME or ACA_SHORT=@ACA_NAME ");
            SqlParameter[] parameter = new SqlParameter[1]
            {
                new SqlParameter("@ACA_NAME",SqlDbType.NVarChar,50),
            };
            parameter[0].Value = AcademyName;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            List<BoAcademy> list = GetList(table);
            return list!=null&&list.Count>0?list:null;
        }

        public List<BoAcademy> GetList(DataTable table)
        {
            List<BoAcademy> list = new List<BoAcademy>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoAcademy model = new BoAcademy();
                    model.ACA_ID = (string)table.Rows[i]["ACA_ID"];
                    model.ACA_NAME = table.Rows[i]["ACA_NAME"]== System.DBNull.Value ? "":(string)table.Rows[i]["ACA_NAME"];
                    model.ACA_CODE = table.Rows[i]["ACA_CODE"]== System.DBNull.Value ? "":(string)table.Rows[i]["ACA_CODE"];
                    model.ACA_ENNAME = table.Rows[i]["ACA_ENNAME"]== System.DBNull.Value ? "":(string)table.Rows[i]["ACA_ENNAME"];
                    model.ACA_IsShow = table.Rows[i]["ACA_IsShow"]== System.DBNull.Value ? true:(bool)table.Rows[i]["ACA_IsShow"];
                    model.ACA_IsTeach = table.Rows[i]["ACA_IsTeach"]== System.DBNull.Value ? "":(string)table.Rows[i]["ACA_IsTeach"];
                    model.ACA_REMARK = table.Rows[i]["ACA_REMARK"]== System.DBNull.Value ? "":(string)table.Rows[i]["ACA_REMARK"];
                    model.ACA_SHORT = table.Rows[i]["ACA_SHORT"]== System.DBNull.Value ? "":(string)table.Rows[i]["ACA_SHORT"];
                    model.ACA_LOCAL = table.Rows[i]["ACA_LOCAL"] == System.DBNull.Value ? "" : (string)table.Rows[i]["ACA_LOCAL"];
                    model.LOC_NAME = table.Rows[i]["LOC_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["LOC_NAME"];
                    list.Add(model);
                }
            }
            return list;
        }

         /// <summary>
        /// 通过学院的院系号来获取学院对象
        /// </summary>
        /// <param name="AcademyCode">学院号</param>
        /// <returns>返回对象</returns>
        public BoAcademy getAcademyByCode(string AcademyCode)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ACA_ID,ACA_NAME,ACA_IsShow,ACA_CODE,ACA_SHORT,ACA_ENNAME,ACA_IsTeach,ACA_REMARK,ACA_LOCAL,LOC_NAME ");
            sql.Append("FROM Academy AS A LEFT JOIN Local AS L ON A.ACA_LOCAL=L.LOC_ID ");
            sql.Append(" WHERE ");
            sql.Append(" ACA_IsShow='true' and ACA_CODE=@ACA_CODE");
            SqlParameter[] parameter = new SqlParameter[1]
            {
                new SqlParameter("@ACA_CODE",SqlDbType.NVarChar,50),
            };
            parameter[0].Value = AcademyCode;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            List<BoAcademy> list = GetList(table);
            return list!=null&&list.Count>0?list[0]:null;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoAcademy model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@ACA_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@ACA_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@ACA_CODE",SqlDbType.NVarChar,50),
                new SqlParameter("@ACA_ENNAME",SqlDbType.NVarChar,50),
                new SqlParameter("@ACA_IsShow",SqlDbType.Bit,2),
                new SqlParameter("@ACA_IsTeach",SqlDbType.NVarChar,50),
                new SqlParameter("@ACA_REMARK",SqlDbType.NVarChar,50),
                new SqlParameter("@ACA_SHORT",SqlDbType.NVarChar,50),
                new SqlParameter("@ACA_LOCAL",SqlDbType.NVarChar,50),
                new SqlParameter("@LOC_NAME",SqlDbType.NVarChar,50)
            };
            parameters[0].Value = model.ACA_ID;
            parameters[1].Value = model.ACA_NAME;
            parameters[2].Value = model.ACA_CODE;
            parameters[3].Value = model.ACA_ENNAME;
            parameters[4].Value = model.ACA_IsShow;
            parameters[5].Value = model.ACA_IsTeach;
            parameters[6].Value = model.ACA_REMARK;
            parameters[7].Value = model.ACA_SHORT;
            parameters[8].Value = model.ACA_LOCAL;
            parameters[9].Value = model.LOC_NAME;
            return parameters;
        }
    }
}